import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class demo {
    public static void main(String[] args) throws SQLException {
        Add_MySQL add_mySQL = new Add_MySQL();
        add_mySQL.add(1,"李华");

        Delete_MySQL deleteMySQL = new Delete_MySQL();
        deleteMySQL.delete("李华");

    }
}


class Add_MySQL{

     public void add(int id, String name) throws SQLException {

         DataSource dataSource = new MysqlDataSource();
         ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/db05?characterEncoding=utf8&useSSL=false");
         ((MysqlDataSource) dataSource).setUser("root");
         ((MysqlDataSource) dataSource).setPassword("123456");

         Connection connection = dataSource.getConnection();

         String sql = "insert into student values(?,?)";
         PreparedStatement preparedStatement = connection.prepareStatement(sql);
         preparedStatement.setInt(1,id);
         preparedStatement.setString(2,name);

         int n = preparedStatement.executeUpdate();
         System.out.println("改变行数" + ":" + n);

         preparedStatement.close();
         connection.close();

     }
}

class Delete_MySQL {

    public void delete(int id) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/db05?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("123456");

        Connection connection = dataSource.getConnection();

        String sql = "delete from student where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,id);

        int n = preparedStatement.executeUpdate();
        System.out.println("改变了"+n+"行");

        preparedStatement.close();
        connection.close();
    }

    public void delete(String name) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/db05?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("123456");

        Connection connection = dataSource.getConnection();

        String sql = "delete from student where name = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,name);

        int n = preparedStatement.executeUpdate();
        System.out.println("改变了"+n+"行");

        preparedStatement.close();
        connection.close();
    }

}

class Update_MySQL{
    public void update(String name,int id) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/db05?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("123456");

        Connection connection = dataSource.getConnection();

        String sql = "update student set id = ? where name = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,id);
        preparedStatement.setString(2,name);

        int n = preparedStatement.executeUpdate();
        System.out.println("删除操作，改变了"+n+"行");

        preparedStatement.close();
        connection.close();
    }

}

class Select_MySQL{
    public void select() throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/db05?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("123456");

        Connection connection = dataSource.getConnection();

        String sql = "select * from student";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            System.out.println(id + ":" + name);
        }

        preparedStatement.close();
        connection.close();
    }
}